package cn.com.libertymutual.sp.dao;

import java.util.List;
import java.util.Optional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import cn.com.libertymutual.sp.bean.TbSpUser;

/**
 * @author AoYi
 * 
 * 注意@Modifying注解需要使用clearAutomatically=true;
 * 同一接口更新后立即查询获得更新后的数据,默认false查询还是更新前的数据
 *
 */
@Repository
public interface UserDao extends PagingAndSortingRepository<TbSpUser, Integer>, JpaSpecificationExecutor<TbSpUser> {
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set password = :pwd where state=1 and userCode = :userCode")
	public int initUserPwd(@Param("pwd") String pwd, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set userName = :userName,idNumber = :idNumber,idType = :idType where state=1 and userCode = :userCode")
	public int updateRealNameByUserCode(@Param("userName") String userName, @Param("idNumber") String idNumber, @Param("idType") String idType,
			@Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set password = :pwd where state=1 and userCode = :userCode")
	public int updatePwdByUserCode(@Param("pwd") String pwd, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set userName = :userName where state=1 and userCode = :userCode")
	public int updateUserNameByUserCode(@Param("userName") String userName, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set applyAgrNoType = :applyAgrNoType, areaCode = :areaCode, areaName = :areaName where state=1 and userCode = :userCode")
	public int updateAgreementNoByApply(@Param("applyAgrNoType") String applyAgrNoType, @Param("areaCode") String areaCode,
			@Param("areaName") String areaName, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set userType = :userType, branchCode = :branchCode, agrementNo = :agrementNo, "
			+ "saleCode = :saleCode, saleName = :saleName, channelCode = :channelCode, channelName = :channelName, "
			+ "areaCode = :areaCode,areaName = :areaName, storeFlag = :storeFlag where state=1 and userCode = :userCode")
	public int updateStoreState(@Param("userType") String userType, @Param("branchCode") String branchCode, @Param("agrementNo") String agrementNo,
			@Param("saleCode") String saleCode, @Param("saleName") String saleName, @Param("channelCode") String channelCode,
			@Param("channelName") String channelName, @Param("areaCode") String areaCode, @Param("areaName") String areaName,
			@Param("storeFlag") String storeFlag, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set typeState = :typeState where state=1 and userCode = :userCode")
	public int updateTypeState(@Param("typeState") String typeState, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set headUrl = :headUrl where state=1 and userCode = :userCode")
	public int updateHeadUrlByUserCode(@Param("headUrl") String headUrl, @Param("userCode") String userCode);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query("update TbSpUser set mobile = :oldMobile where state=1 and userCode = :userCode")
	public int updateMobileByUserCode(@Param("oldMobile") String oldMobile, @Param("userCode") String userCode);

	@Query("select t from TbSpUser t where t.state=1 and t.userCode = :userCode")
	public TbSpUser findByUserCode(@Param("userCode") String userCode);

	@Query("select t from TbSpUser t where t.userCode = :userCode")
	public TbSpUser findByUserCodeAll(@Param("userCode") String userCode);

	@Query("from TbSpUser t where t.userCode = :userCode")
	public TbSpUser findByUserCodeBase(@Param("userCode") String userCode);

	@Query("from TbSpUser t where t.userCodeBs = :userCode")
	public List<TbSpUser> findUserCodeBs(@Param("userCode") String userCode);

	@Query("select t from TbSpUser t where userCode = :userCode or userCodeBs = :userCodeBs")
	public List<TbSpUser> findByUserCodeBoth(@Param("userCode") String userCode, @Param("userCodeBs") String userCodeBs);

	@Query("select t from TbSpUser t where state=1 and id = :id")
	public Optional<TbSpUser> findById(@Param("id") Integer id);

	@Query("select t from TbSpUser t where state=1 and mobile = :mobile order by id desc")
	public List<TbSpUser> findByMobile(@Param("mobile") String mobile);

	@Query("select t from TbSpUser t where state=1 and idNumber = :idNumber")
	public List<TbSpUser> findByIdNumber(@Param("idNumber") String idNumber);

	@Query("select t from TbSpUser t where state=1 and idNumber = :idNumber and registerType = :registerType")
	public List<TbSpUser> findByIdNumber(@Param("idNumber") String idNumber, @Param("registerType") String registerType);

	// @Query("select t from TbSpUser t where state=1 and idNumber = :idNumber and
	// id!= :id")
	// public List<TbSpUser> findByIdNumber(@Param("idNumber") String idNumber,
	// @Param("id") Integer id);
	//
	@Query("select t from TbSpUser t where state=1 and agrementNo = :agrementNo")
	public List<TbSpUser> findByAgrementNo(@Param("agrementNo") String agrementNo);

	@Query("select t from TbSpUser t where state=1 and wxOpenId = :wxOpenId")
	public List<TbSpUser> findByWxOpenId(@Param("wxOpenId") String wxOpenId);

	@Query("select userCode from TbSpUser where state=1 and id = (select max(id) as id from TbSpUser)")
	public String findMaxIdAndCodeOfAll();

	// 查询所有店主
	@Query("select userCode from TbSpUser where state=1 and storeFlag=1")
	public List<String> findAllShopUser();

	@Query(value = "select t.* from tb_sp_user t where t.STATE=1 and t.STORE_FLAG=1 and t.USER_CODE not in (select te.USER_CODE from tb_sp_exclusive_product te where te.PRODUCT_ID = ?1)", nativeQuery = true)
	public List<TbSpUser> findNotExclusiveUser(Integer productId);

	@Transactional
	@Modifying
	@Query("update TbSpUser set shopName = :shopName ,shopIntroduct=:shopIntroduct where state=1 and storeFlag=1 and userCode = :userCode")
	public int updateShopName(@Param("userCode") String userCode, @Param("shopName") String shopName, @Param("shopIntroduct") String shopIntroduct);

	@Query("select t from TbSpUser t where state=1 and id >= :sid and id<= :eid")
	public List<TbSpUser> findByGTId(@Param("sid") Integer sid, @Param("eid") Integer eid);

	@Query("select t from TbSpUser t where state=1 and userName like %?1%")
	public List<TbSpUser> findByUserName(String userName);

	@Transactional
	@Modifying
	@Query("update TbSpUser set configSerialNo = :no where state=1 and storeFlag=1 and userCode = :userCode")
	public void updateConfigSerialNo(@Param("userCode") String userCode, @Param("no") Integer no);

	@Query("select t from TbSpUser t where t.state=1 and t.comCode =?1")
	public Page<TbSpUser> findByComCode(String comCode, Pageable pageable);

	@Query("select t from TbSpUser t where t.state=1 and t.comCode =?1")
	public List<TbSpUser> findByComCode(String comCode);

	@Query("select t from TbSpUser t where t.state=1 and t.branchCode =?1 or t.areaCode = ?1")
	public List<TbSpUser> findByBranchCode(String code);
}
